package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.SdCityDbAttackedAnalysis;
import com.youan.log.modules.audit.service.ISdCityDbAttackedAnalysisService;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

public class SdCityDbAttackedAnalysisDataExtract {

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private ISdCityDbAttackedAnalysisService sdCityDbAttackedAnalysisService;
    private JdbcTemplate jdbcTemplate;
    private List<SdCityDbAttackedAnalysis> sdCityDbAttackedAnalysisList = new ArrayList<>();
    private int BATCH_SIZE = 5000;

    public SdCityDbAttackedAnalysisDataExtract(String date, ThreadTaskProgressor taskProgressor) {
        this.date = date;
        this.taskProgressor = taskProgressor;
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        sdCityDbAttackedAnalysisService = SpringUtil.getBean(ISdCityDbAttackedAnalysisService.class);

        taskProgressor.updateProgressTotal(taskProgressor.getProgressTotalNum() + getCount());
    }

    public void extract() {
        String sql = "SELECT\n" +
                "  a.visit_date date,\n" +
                "  IFNULL(\n" +
                "    a.source_city,\n" +
                "    '未知城市'\n" +
                "  ) attacked_city,\n" +
                "  a.dest_country attack_country,\n" +
                "  0 attacked_total_count,\n" +
                "  0 attacked_success_count,\n" +
                "  0 attacked_failure_count,\n" +
                "  0 blast_success_count,\n" +
                "  0 blast_failure_count\n" +
                "FROM\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_statistical\n" +
                "    WHERE\n" +
                "      visit_date = '" + this.date + "'\n" +
                "  ) a,\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_attack_analysis\n" +
                "    WHERE\n" +
                "      date = '" + this.date + "'\n" +
                "  ) b,\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_attacked_analysis\n" +
                "    WHERE\n" +
                "      date = '" + this.date + "'\n" +
                "    AND province = '山东'\n" +
                "  ) c\n" +
                "WHERE\n" +
                "  a.source_ip = c.attacked_ip\n" +
                "AND a.dest_ip = b.attacker_ip\n" +
                "GROUP BY\n" +
                "  a.visit_date,\n" +
                "  a.source_city,\n" +
                "  a.dest_country";
        buildAndSaveEntity(sql);

        sql = "SELECT\n" +
                "  a.visit_date date,\n" +
                "  IFNULL(a.dest_city, '未知城市') attacked_city,\n" +
                "  a.source_country attack_country,\n" +
                "  0 attacked_total_count,\n" +
                "  0 attacked_success_count,\n" +
                "  0 attacked_failure_count,\n" +
                "  0 blast_success_count,\n" +
                "  0 blast_failure_count\n" +
                "FROM\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_statistical\n" +
                "    WHERE\n" +
                "      visit_date = '" + this.date + "'\n" +
                "  ) a,\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_attack_analysis\n" +
                "    WHERE\n" +
                "      date = '" + this.date + "'\n" +
                "  ) b,\n" +
                "  (\n" +
                "    SELECT\n" +
                "      *\n" +
                "    FROM\n" +
                "      log_attacked_analysis\n" +
                "    WHERE\n" +
                "      date = '" + this.date + "'\n" +
                "    AND province = '山东'\n" +
                "  ) c\n" +
                "WHERE\n" +
                "  a.dest_ip = c.attacked_ip\n" +
                "AND a.source_ip = b.attacker_ip\n" +
                "AND NOT EXISTS (\n" +
                "  SELECT\n" +
                "    *\n" +
                "  FROM\n" +
                "    log_sd_city_db_attacked_analysis s\n" +
                "  WHERE\n" +
                "    a.source_country = s.attack_country\n" +
                "  AND a.dest_city = s.attacked_city\n" +
                "  AND date = '" + this.date + "'\n" +
                ")\n" +
                "GROUP BY\n" +
                "  a.visit_date,\n" +
                "  a.dest_city,\n" +
                "  a.source_country";

        buildAndSaveEntity(sql);

        // 一次性更新被攻击总数
        String updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET s.attacked_total_count = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(a.visit_number)\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_statistical\n" +
                "    WHERE\n" +
                "     visit_date = '" + this.date + "'\n" +
                "   ) a,\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_attack_analysis\n" +
                "    WHERE\n" +
                "     date = '" + this.date + "'\n" +
                "   ) b,\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_attacked_analysis\n" +
                "    WHERE\n" +
                "     date = '" + this.date + "'\n" +
                "    AND province = '山东'\n" +
                "   ) c\n" +
                "  WHERE\n" +
                "   a.source_ip = c.attacked_ip\n" +
                "  AND a.dest_ip = b.attacker_ip\n" +
                "  AND s.attacked_city = a.source_city\n" +
                "  AND s.attack_country = a.dest_country\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_total_count\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET s.attacked_total_count = IFNULL(\n" +
                " (\n" +
                "  SELECT\n" +
                "   sum(a.visit_number)\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_statistical\n" +
                "    WHERE\n" +
                "     visit_date = '" + this.date + "'\n" +
                "   ) a,\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_attack_analysis\n" +
                "    WHERE\n" +
                "     date = '" + this.date + "'\n" +
                "   ) b,\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     log_attacked_analysis\n" +
                "    WHERE\n" +
                "     date = '" + this.date + "'\n" +
                "    AND province = '山东'\n" +
                "   ) c\n" +
                "  WHERE\n" +
                "   a.dest_ip = c.attacked_ip\n" +
                "  AND a.source_ip = b.attacker_ip\n" +
                "  AND s.attacked_city = a.dest_city\n" +
                "  AND s.attack_country = a.source_country\n" +
                " ),\n" +
                " 0\n" +
                ") + attacked_total_count\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        // 一次性更新被攻击失败数

        updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET attacked_failure_count = (\n" +
                " SELECT\n" +
                "  IFNULL(sum(visit_number), 0)\n" +
                " FROM\n" +
                "  log_statistical\n" +
                " WHERE\n" +
                "  visit_date = '" + this.date + "'\n" +
                " AND issue_type IN ('10050001084')\n" +
                " AND dest_country = s.attack_country\n" +
                " AND source_city = s.attacked_city\n" +
                " AND dest_ip IN (\n" +
                "  SELECT\n" +
                "   attacker_ip\n" +
                "  FROM\n" +
                "   log_attack_analysis WHERE date = '" + this.date + "'\n" +
                " )\n" +
                ")\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        // 一次性更新爆破成功数

        updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET blast_success_count = (\n" +
                " SELECT\n" +
                "  IFNULL(sum(visit_number), 0)\n" +
                " FROM\n" +
                "  log_statistical\n" +
                " WHERE\n" +
                "  visit_date = '" + this.date + "'\n" +
                " AND issue_type = '10050001476'\n" +
                " AND dest_country = s.attack_country\n" +
                " AND source_city = s.attacked_city\n" +
                " AND dest_ip IN (\n" +
                "  SELECT\n" +
                "   attacker_ip\n" +
                "  FROM\n" +
                "   log_attack_analysis WHERE date = '" + this.date + "' \n" +
                " )\n" +
                ")\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        // 一次性更新被攻击成功数
        updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET s.attacked_success_count = attacked_total_count - s.attacked_failure_count\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";

        jdbcTemplate.execute(updateSql);

        // 一次性更新爆破失败数

        updateSql = "UPDATE log_sd_city_db_attacked_analysis s\n" +
                "SET s.blast_failure_count = attacked_failure_count\n" +
                "WHERE\n" +
                " date = '" + this.date + "'";
        jdbcTemplate.execute(updateSql);
    }

    private void buildAndSaveEntity(String sql) {

        sdCityDbAttackedAnalysisList = new ArrayList<>();

        jdbcTemplate.query(sql, rs -> {
            SdCityDbAttackedAnalysis sdCityDbAttackedAnalysis = new SdCityDbAttackedAnalysis();
            sdCityDbAttackedAnalysis.setDate(rs.getString("date"));
            sdCityDbAttackedAnalysis.setAttackedCity(rs.getString("attacked_city"));
            sdCityDbAttackedAnalysis.setAttackCountry(rs.getString("attack_country"));
            sdCityDbAttackedAnalysis.setAttackedTotalCount(rs.getInt("attacked_total_count"));
            sdCityDbAttackedAnalysis.setAttackedSuccessCount(rs.getInt("attacked_success_count"));
            sdCityDbAttackedAnalysis.setAttackedFailureCount(rs.getInt("attacked_failure_count"));
            sdCityDbAttackedAnalysis.setBlastSuccessCount(rs.getInt("blast_success_count"));
            sdCityDbAttackedAnalysis.setBlastFailureCount(rs.getInt("blast_failure_count"));


            sdCityDbAttackedAnalysisList.add(sdCityDbAttackedAnalysis);
            if(sdCityDbAttackedAnalysisList.size() >= BATCH_SIZE) {
                sdCityDbAttackedAnalysisService.saveBatch(sdCityDbAttackedAnalysisList);
                taskProgressor.addProgressNum(sdCityDbAttackedAnalysisList.size());
                sdCityDbAttackedAnalysisList = new ArrayList<>();
            }
        });

        sdCityDbAttackedAnalysisService.saveBatch(sdCityDbAttackedAnalysisList);
        taskProgressor.addProgressNum(sdCityDbAttackedAnalysisList.size());
    }

    private int getCount() {
        String sql = "SELECT\n" +
                " count(1)\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   date,\n" +
                "   attacked_city,\n" +
                "   attack_country,\n" +
                "   sum(attacked_total_count) attacked_total_count\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     *\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       a.visit_date date,\n" +
                "       IFNULL(\n" +
                "        a.source_city,\n" +
                "        '未知城市'\n" +
                "       ) attacked_city,\n" +
                "       a.dest_country attack_country,\n" +
                "       sum(visit_number) attacked_total_count\n" +
                "      FROM\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_statistical\n" +
                "        WHERE\n" +
                "         visit_date = '" + this.date + "'\n" +
                "       ) a,\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_attack_analysis\n" +
                "        WHERE\n" +
                "         date = '" + this.date + "'\n" +
                "       ) b,\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_attacked_analysis\n" +
                "        WHERE\n" +
                "         date = '" + this.date + "'\n" +
                "        AND province = '山东'\n" +
                "       ) c\n" +
                "      WHERE\n" +
                "       a.source_ip = c.attacked_ip\n" +
                "      AND a.dest_ip = b.attacker_ip\n" +
                "      GROUP BY\n" +
                "       a.visit_date,\n" +
                "       a.source_city,\n" +
                "       a.dest_country\n" +
                "     ) a\n" +
                "    UNION ALL\n" +
                "     (\n" +
                "      SELECT\n" +
                "       a.visit_date date,\n" +
                "       IFNULL(a.dest_city, '未知城市') attacked_city,\n" +
                "       a.source_country attack_country,\n" +
                "       sum(visit_number) attacked_total_count\n" +
                "      FROM\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_statistical\n" +
                "        WHERE\n" +
                "         visit_date = '" + this.date + "'\n" +
                "       ) a,\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_attack_analysis\n" +
                "        WHERE\n" +
                "         date = '" + this.date + "'\n" +
                "       ) b,\n" +
                "       (\n" +
                "        SELECT\n" +
                "         *\n" +
                "        FROM\n" +
                "         log_attacked_analysis\n" +
                "        WHERE\n" +
                "         date = '" + this.date + "'\n" +
                "        AND province = '山东'\n" +
                "       ) c\n" +
                "      WHERE\n" +
                "       a.dest_ip = c.attacked_ip\n" +
                "      AND a.source_ip = b.attacker_ip\n" +
                "      GROUP BY\n" +
                "       a.visit_date,\n" +
                "       a.dest_city,\n" +
                "       a.source_country\n" +
                "     )\n" +
                "   ) b\n" +
                "  GROUP BY\n" +
                "   date,\n" +
                "   attacked_city,\n" +
                "   attack_country\n" +
                " ) c";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

}
